USE master;
GO

DROP PROCEDURE IF EXISTS sp_hexfrombin;
GO
CREATE PROCEDURE sp_hexfrombin
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514) = '0x';
DECLARE @i int = 1;
DECLARE @length int = DATALENGTH(@binvalue);
DECLARE @hexstring char(16) = '0123456789ABCDEF';

WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int = CONVERT(int, SUBSTRING(@binvalue,@i,1));
  DECLARE @firstint int = FLOOR(@tempint/16);
  DECLARE @secondint int = @tempint - (@firstint*16);
  SELECT @charvalue += 
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1);
  SELECT @i += 1;
END

SELECT @hexvalue = @charvalue;
GO
 
DROP PROCEDURE IF EXISTS sp_help_revlogin;
GO
CREATE PROCEDURE sp_help_revlogin
	@loginname sysname = NULL
AS
DECLARE @name sysname;
DECLARE @type varchar (1);
DECLARE @has_access int;
DECLARE @denylogin int;
DECLARE @is_disabled int;
DECLARE @PWDvarbinary varbinary (256);
DECLARE @PWDstring varchar (514);
DECLARE @SIDvarbinary varbinary (85);
DECLARE @SIDstring varchar (514);
DECLARE @tempstring varchar (1024);
DECLARE @is_policy_checked varchar (3);
DECLARE @is_expiration_checked varchar (3);
DECLARE @defaultdb sysname;
 
IF (@loginname IS NULL)
  DECLARE login_cursor CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin
	  FROM sys.server_principals p LEFT JOIN sys.syslogins l
      ON (l.name = p.name) WHERE p.type IN ('S', 'G', 'U') AND p.name <> 'sa';
ELSE
  DECLARE login_cursor CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin
	  FROM sys.server_principals p LEFT JOIN sys.syslogins l
      ON (l.name = p.name) WHERE p.type IN ('S', 'G', 'U') AND p.name = @loginname;

OPEN login_cursor
FETCH NEXT FROM login_cursor INTO @SIDvarbinary, @name, @type, @is_disabled, @defaultdb, @has_access, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.';
  CLOSE login_cursor;
  DEALLOCATE login_cursor;
  RETURN -1;
END
SET @tempstring = '/* sp_help_revlogin script ';
PRINT @tempstring;
SET @tempstring = '** Generated ' + CONVERT(varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */';
PRINT @tempstring;
PRINT '';
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT '';
    SET @tempstring = '-- Login: ' + @name;
    PRINT @tempstring;
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group
      SET @tempstring = 'CREATE LOGIN ' + QUOTENAME(@name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']';
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
        SET @PWDvarbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS varbinary (256));
        EXECUTE sp_hexfrombin @PWDvarbinary, @PWDstring OUT;
        EXECUTE sp_hexfrombin @SIDvarbinary,@SIDstring OUT;
 
        -- obtain password policy state
        SELECT @is_policy_checked =
			CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name;
        SELECT @is_expiration_checked =
			CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name;
 
            SET @tempstring = 'CREATE LOGIN ' + QUOTENAME(@name) + ' WITH PASSWORD = ' + @PWDstring + ' HASHED, SID = ' + @SIDstring + ', DEFAULT_DATABASE = [' + @defaultdb + ']';

        IF (@is_policy_checked IS NOT NULL)
        BEGIN
          SET @tempstring = @tempstring + ', CHECK_POLICY = ' + @is_policy_checked;
        END
        IF (@is_expiration_checked IS NOT NULL)
        BEGIN
          SET @tempstring = @tempstring + ', CHECK_EXPIRATION = ' + @is_expiration_checked;
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tempstring = @tempstring + '; DENY CONNECT SQL TO ' + QUOTENAME(@name);
    END
    ELSE IF (@has_access = 0)
    BEGIN -- login exists but does not have access
      SET @tempstring = @tempstring + '; REVOKE CONNECT SQL TO ' + QUOTENAME(@name);
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tempstring = @tempstring + '; ALTER LOGIN ' + QUOTENAME(@name) + ' DISABLE';
    END
    PRINT @tempstring;
  END

  FETCH NEXT FROM login_cursor INTO @SIDvarbinary, @name, @type, @is_disabled, @defaultdb, @has_access, @denylogin;
END
CLOSE login_cursor;
DEALLOCATE login_cursor;

RETURN 0;
GO

EXECUTE sp_help_revlogin;

-- cleanup
DROP PROCEDURE IF EXISTS sp_hexfrombin, sp_help_revlogin;
GO